﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

namespace AxeFrog.Data
{
	public static class ConnectionContextExtensions
	{
		public delegate IDbCommand ParamsHandler<T>(IDbCommand cmd, T entity);

		/// <summary>
		/// creates a new IDbCommand with CommandType.Text and the specified CommandText and attaches
		/// it to the current transaction, if any
		/// </summary>
		public static IDbCommand CreateCommand(this ConnectionContext cc, string commandText)
		{
			return cc.Connection.CreateCommand(commandText).SetTransaction(cc.Transaction);
		}

		/// <summary>
		/// creates a new IDbCommand with CommandType.Text and attaches it to the current transaction, if any
		/// </summary>
		public static IDbCommand CreateCommand(this ConnectionContext cc)
		{
			return cc.Connection.CreateCommand().SetTransaction(cc.Transaction);
		}

		/// <summary>
		/// creates a new IDbCommand with the specified CommandType and CommandText and attaches
		/// it to the current transaction, if any
		/// </summary>
		public static IDbCommand CreateCommand(this ConnectionContext cc, string commandText, CommandType commandType)
		{
			return cc.Connection.CreateCommand(commandText, commandType).SetTransaction(cc.Transaction);
		}

		/// <summary>
		/// creates a new IDbCommand with the specified CommandText, attaches it to the current transaction, if any,
		/// then calls <param name="addParameters"/> and passes in the specified entity T
		/// </summary>
		public static IDbCommand CreateCommand<T>(this ConnectionContext cc, string commandText, T entity, IDbConnectionExtensions.ParamsHandler<T> addParameters)
		{
			return cc.Connection.CreateCommand(commandText, entity, addParameters).SetTransaction(cc.Transaction);
		}

		/// <summary>
		/// creates a new IDbCommand with the specified CommandText and CommandType, attaches it to the current transaction, if any,
		/// then calls <param name="addParameters"/> and passes in the specified entity T
		/// </summary>
		public static IDbCommand CreateCommand<T>(this ConnectionContext cc, string commandText, CommandType commandType, T entity, IDbConnectionExtensions.ParamsHandler<T> addParameters)
		{
			return cc.Connection.CreateCommand(commandText, commandType, entity, addParameters).SetTransaction(cc.Transaction);
		}

		/// <summary>
		/// creates a new IDbCommand with CommandText specified by <param name="procName"/>, sets the CommandType to StoredProcedure
		/// and attaches it to the current transaction, if any
		/// </summary>
		public static IDbCommand CreateProcedure(this ConnectionContext cc, string procName)
		{
			return cc.Connection.CreateProcedure(procName).SetTransaction(cc.Transaction);
		}

		/// <summary>
		/// creates a new IDbCommand with CommandText specified by <param name="procName"/>, sets the CommandType to StoredProcedure
		/// and attaches it to the current transaction, if any,
		/// then calls <param name="addParameters"/> and passes in the specified entity T
		/// </summary>
		public static IDbCommand CreateProcedure<T>(this ConnectionContext cc, string procName, T entity, IDbConnectionExtensions.ParamsHandler<T> addParameters)
		{
			return cc.Connection.CreateProcedure(procName, entity, addParameters).SetTransaction(cc.Transaction);
		}

		private class SqlStatements
		{
			public string Insert { get; private set; }
			public string Update { get; private set; }
			public string Select { get; private set; }
			public string SelectWhere { get; private set; }
			public string SelectWhereOrderBy { get; private set; }
			public string SelectManyByID { get; private set; }
			public string SelectManyWhere { get; private set; }
			public string SelectManyWhereOrderBy { get; private set; }
			public string SelectAll { get; private set; }
			public string Count { get; private set; }
			public string Delete { get; private set; }

			public static SqlStatements Create<TEntity, TID>(ConnectionContext cc)
				where TEntity : IIdentifiable<TID>
			{
				var fields = RepositoryHelper.EnumerateAttributedFields<TEntity>();
				var sql = new SqlStatements();
				sql.Insert = cc.ConnectionProvider.AppendIdentitySelectorTo(RepositoryHelper.CreateInsertStatement<TEntity>());
				sql.Update = RepositoryHelper.CreateUpdateStatement<TEntity>();
				sql.Select = string.Format(cc.ConnectionProvider.ModifySqlToSetRecordLimit(RepositoryHelper.CreateSelectStatement<TEntity>(),0), 1);
				sql.SelectManyByID = RepositoryHelper.CreateSelectStatement<TEntity>("ID IN ({0})").Compile();
				sql.SelectAll = RepositoryHelper.CreateSelectStatement<TEntity>(null).Compile();
				var count = RepositoryHelper.CreateSelectStatement<TEntity>(null);
				count.SelectFields = "COUNT(*)";
				sql.Count = count.Compile();
				sql.Delete = RepositoryHelper.CreateDeleteStatement<TEntity>();
				return sql;
			}

			private Dictionary<Type, SqlSelectStatement> _typeSelectMap = new Dictionary<Type, SqlSelectStatement>();
			public SqlSelectStatement CreateSelect<TEntity>()
			{
				SqlSelectStatement sql;
				lock(this)
				{
					if(!_typeSelectMap.TryGetValue(typeof(TEntity), out sql))
					{
						sql = RepositoryHelper.CreateSelectStatement<TEntity>(null);
						_typeSelectMap.Add(typeof(TEntity), sql);
					}
				}
				return sql.Clone();
			}
		}
		private static Dictionary<Type, SqlStatements> _sqlStatements = new Dictionary<Type, SqlStatements>();
		private static SqlStatements GetSqlStatements<TEntity, TID>(ConnectionContext cc)
			where TEntity : IIdentifiable<TID>
		{
			// we keep a cache of statements because they only need to be calculated at first request
			SqlStatements sql;
			lock(_sqlStatements)
				if(!_sqlStatements.TryGetValue(typeof(TEntity), out sql))
				{
					sql = SqlStatements.Create<TEntity, TID>(cc);
					_sqlStatements.Add(typeof(TEntity), sql);
				}
			return sql;
		}

		public static void Store<TEntity, TID>(this ConnectionContext cc, TEntity entity)
			where TEntity : IIdentifiable<TID>
		{
			SqlStatements sql = GetSqlStatements<TEntity, TID>(cc);

			if(EqualityComparer<TID>.Default.Equals(entity.ID, default(TID)))
			{
				var cmd = cc.CreateCommand(sql.Insert)
					.AddNonIdParametersFrom(entity);
				var dr = cmd.ExecuteReader();
				dr.ReadNext();
				dr.SaveResult<TID>(h => entity.ID = h);
				dr.Dispose();
			}
			else
			{
				cc.CreateCommand(sql.Update)
					.AddParametersFrom(entity)
					.Execute()
					.Dispose();
			}
		}

		public static void StoreMany<TEntity, TID>(this ConnectionContext cc, IEnumerable<TEntity> entities)
			where TEntity : IIdentifiable<TID>
		{
			cc.StoreMany<TEntity, TID>(entities, k => { });
		}

		public static void StoreMany<TEntity, TID>(this ConnectionContext cc, IEnumerable<TEntity> entities, Action<TEntity> action)
			where TEntity : IIdentifiable<TID>
		{
			var inserts = new List<TEntity>();
			var updates = new List<TEntity>();
			foreach(var entity in entities)
				if(EqualityComparer<TID>.Default.Equals(entity.ID, default(TID)))
					inserts.Add(entity);
				else
					updates.Add(entity);

			SqlStatements sql = GetSqlStatements<TEntity, TID>(cc);
			using(var trans = cc.BeginTransaction())
			{
				Dictionary<PropertyInfo, IDataParameter> prms;
				using(var cmd = cc.CreateCommand(sql.Insert).AddEmptyNonIdParametersFrom<TEntity>(out prms))
					foreach(var entity in inserts)
					{
						foreach(var kvp in prms)
							kvp.Value.SetValue(kvp.Key.GetValue(entity, null));
						using(var reader = cmd.ExecuteReader())
							reader.ReadNext().SaveResult<TID>(h => entity.ID = h);
						action(entity);
					}
				using(var cmd = cc.CreateCommand(sql.Update).AddEmptyParametersFrom<TEntity>(out prms))
					foreach(var entity in updates)
					{
						foreach(var kvp in prms)
							kvp.Value.SetValue(kvp.Key.GetValue(entity, null));
						cmd.ExecuteNonQuery();
						action(entity);
					}
				trans.Commit();
			}
		}

		public static TEntity Select<TEntity, TID>(this ConnectionContext cc, TID id)
			where TEntity : IIdentifiable<TID>
		{
			SqlStatements sql = GetSqlStatements<TEntity, TID>(cc);
			return cc.CreateCommand(sql.Select)
				.AddParameter("@ID", id)
				.ExecuteReader()
				.ReadAndReturn<TEntity>();
		}

		public static TEntity Select<TEntity, TID>(this ConnectionContext cc, Expression<Func<TEntity, bool>> where, params string[] orderBy)
			where TEntity : IIdentifiable<TID>
		{
			SqlStatements sql = GetSqlStatements<TEntity, TID>(cc);
			var select = sql.CreateSelect<TEntity>();
			var cmd = cc.CreateCommand();
			select.Where = cc.CreateExpressionVisitor(cmd).Translate(where);
			cmd.CommandText = string.Format(cc.ConnectionProvider.ModifySqlToSetRecordLimit(select, 0), 1);
			return cmd.ExecuteReader()
				.ReadAndReturn<TEntity>();
		}

		public static List<TEntity> SelectMany<TEntity, TID>(this ConnectionContext cc, IEnumerable<TID> idList)
			where TEntity : IIdentifiable<TID>
		{
			List<TEntity> list = new List<TEntity>();
			if(idList.Count() == 0)
				return list;
			SqlStatements sql = GetSqlStatements<TEntity, TID>(cc);
			return cc.CreateCommand(string.Format(sql.SelectManyByID, idList.Concat(",")))
				.ExecuteReader()
				.ReadEach(dr => list.Add(dr.MapTo<TEntity>()))
				.Return(list);
		}

		public static List<TEntity> SelectMany<TEntity, TID>(this ConnectionContext cc, Expression<Func<TEntity, bool>> where)
			where TEntity : IIdentifiable<TID>
		{
			SqlStatements sql = GetSqlStatements<TEntity, TID>(cc);
			var select = sql.CreateSelect<TEntity>();
			var cmd = cc.CreateCommand();
			select.Where = cc.CreateExpressionVisitor(cmd).Translate(where);
			cmd.CommandText = select.Compile();
			List<TEntity> list = new List<TEntity>();
			return cmd.ExecuteReader()
				.ReadEach(dr => list.Add(dr.MapTo<TEntity>()))
				.Return(list);
		}

		public static List<TEntity> SelectAll<TEntity, TID>(this ConnectionContext cc)
			where TEntity : IIdentifiable<TID>
		{
			List<TEntity> list = new List<TEntity>();
			SqlStatements sql = GetSqlStatements<TEntity, TID>(cc);
			return cc.CreateCommand(sql.SelectAll)
				.ExecuteReader()
				.ReadEach(dr => list.Add(dr.MapTo<TEntity>()))
				.Return(list);
		}

		public static int Count<TEntity, TID>(this ConnectionContext cc)
			where TEntity : IIdentifiable<TID>
		{
			SqlStatements sql = GetSqlStatements<TEntity, TID>(cc);
			return cc.CreateCommand(sql.Count)
				.ExecuteReader()
				.ReadAndReturn<int>();
		}

		public static int Count<TEntity, TID>(this ConnectionContext cc, Expression<Func<TEntity, bool>> where)
			where TEntity : IIdentifiable<TID>
		{
			SqlStatements sql = GetSqlStatements<TEntity, TID>(cc);
			var select = sql.CreateSelect<TEntity>();
			select.SelectFields = "COUNT(*)";
			var cmd = cc.CreateCommand();
			select.Where = cc.CreateExpressionVisitor(cmd).Translate(where);
			cmd.CommandText = select.Compile(new SqlSelectStatementModifications { DisableOrderBy = true });
			return cmd.ExecuteScalar<int>();
		}

		public static void Delete<TEntity, TID>(this ConnectionContext cc, TID id)
			where TEntity : IIdentifiable<TID>
		{
			SqlStatements sql = GetSqlStatements<TEntity, TID>(cc);
			cc.CreateCommand(sql.Delete)
				.AddParameter("@ID", id)
				.Execute()
				.Dispose();
		}

		public static void Delete<TEntity, TID>(this ConnectionContext cc, Expression<Func<TEntity, bool>> where)
			where TEntity : IIdentifiable<TID>
		{
			var cmd = cc.CreateCommand();
			cmd.CommandText = string.Concat("DELETE FROM [", typeof(TEntity).Name, "] WHERE ",
				cc.CreateExpressionVisitor(cmd).Translate(where));
			cmd.Execute().Dispose();
		}
	}
}